/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.erp.server.DAO;

import com.erp.client.DAO.UserBaseInf;
import com.erp.client.DAO.UserPasswordChange;
import com.erp.server.DB.DbConn;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 *
 * @author Administrator
 */
public class UserDao {

//*****************检查登录用户合法性*******
    public UserBaseInf checkUser(UserBaseInf u) throws SQLException {


        UserBaseInf ub = new UserBaseInf();
        String sql = "SELECT us.*,dep.DepName  FROM [userBaseInf] us INNER JOIN sysdep dep on dep.depid=us.depid where LoginID=? and Password=?";
        Connection conn = DbConn.getCon();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, u.getLoginId());
            ps.setString(2, u.getPassword());
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                ub.setUserId(rs.getInt(1));
                ub.setUserName(rs.getString(2));
                ub.setDepId(rs.getInt(3));
                ub.setLoginId(rs.getString(4));
                ub.setPassword(rs.getString(5));
                ub.setSex(rs.getBoolean(6));
                ub.setUserCode(rs.getString(7));
                ub.setEmpCode(rs.getString(8));
                ub.setLockDate(rs.getDate(9));
                ub.setAllowLogin(rs.getBoolean(10));
                ub.setDelFlag(rs.getBoolean(11));
                ub.setPmroleId(rs.getInt(12));
                ub.setDepName(rs.getString(13));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            conn.close();
        }
        return ub;
    }

// 判断是否存在该用户
    public boolean isHasUser(String username) throws SQLException {
        String sql = "select count(*) from userBaseInf where userName=?";
        Connection conn = DbConn.getCon();
        int count = 0;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            conn.close();
        }

        if (count > 0) {
            return true;
        } else {
            return false;
        }
    }

    // 根据id查找用户
    public UserBaseInf lookupByid(int id) throws SQLException {
        UserBaseInf ub = new UserBaseInf();
        String sql = "SELECT us.*,dep.DepName  FROM [userBaseInf] us INNER JOIN sysdep dep on dep.depid=us.depid where userId=?";
        Connection conn = DbConn.getCon();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ub.setUserId(rs.getInt(1));
                ub.setUserName(rs.getString(2));
                ub.setDepId(rs.getInt(3));
                ub.setLoginId(rs.getString(4));
                ub.setPassword(rs.getString(5));
                ub.setSex(rs.getBoolean(6));
                ub.setUserCode(rs.getString(7));
                ub.setEmpCode(rs.getString(8));
                ub.setLockDate(rs.getDate(9));
                ub.setAllowLogin(rs.getBoolean(10));
                ub.setDelFlag(rs.getBoolean(11));
                ub.setPmroleId(rs.getInt(12));
                ub.setDepName(rs.getString(13));
            //System.out.print(ub.getDepName());
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            conn.close();
        }
        return ub;
    }

    // 删除一个用户
    public boolean delUser(UserBaseInf ub) throws SQLException {
        String sql = "UPDATE userBaseInf  SET DelFlag = 'false' WHERE userid =?";
        Connection conn = DbConn.getCon();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, ub.getUserId());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return false;
        } finally {
            conn.close();
        }
    }

    // 修改一个用户
    public boolean editUser(UserBaseInf ub) throws SQLException {

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
        String s;

        String sql = "UPDATE apollo.dbo.userBaseInf" + "SET userName = ?,DepID = ?,LoginID = ?,Password = ?,Sex = ?,userCode = ?,empCode = ?,lockDate =?" + ",AllowLogin = ?,DelFlag = ?,PMRoleID=?  WHERE userId=?";
        Connection conn = DbConn.getCon();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, ub.getUserName());
            ps.setInt(2, ub.getDepId());
            ps.setString(3, ub.getLoginId());
            ps.setString(4, ub.getPassword());
            ps.setBoolean(5, ub.getSex());
            ps.setString(6, ub.getUserCode());
            ps.setString(7, ub.getEmpCode());
            s = df.format(ub.getLockDate());
            ps.setString(8, s);
            ps.setBoolean(9, ub.getAllowLogin());
            ps.setBoolean(10, ub.getDelFlag());
            ps.setInt(11, ub.getPmroleId());

            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return false;
        } finally {
            conn.close();
        }
    }

    // 增加一个用户
    public boolean addUser(UserBaseInf ub) throws SQLException {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String s;
        String sql = "INSERT INTO userBaseInf (userName,DepID,LoginID,Password,Sex,userCode,empCode,lockDate,AllowLogin,DelFlag,PMRoleID)" + "VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = DbConn.getCon();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, ub.getUserName());
            ps.setInt(2, ub.getDepId());
            ps.setString(3, ub.getLoginId());
            ps.setString(4, ub.getPassword());
            ps.setBoolean(5, ub.getSex());
            ps.setString(6, ub.getUserCode());
            ps.setString(7, ub.getEmpCode());
            s = df.format(new Date());
            ps.setString(8, s);
            ps.setBoolean(9, ub.getAllowLogin());
            ps.setBoolean(10, ub.getDelFlag());
            ps.setInt(11, ub.getPmroleId());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return false;
        } finally {
            conn.close();
        }
    }

    // 修改一个用户
    public boolean ChangePassword(UserPasswordChange ub) throws SQLException {
        String sqlchk = "select count(*) from userBaseInf WHERE userId=? and Password=?";
        Connection connchk = DbConn.getCon();
        int count = 0;
        try {
            PreparedStatement pschk = connchk.prepareStatement(sqlchk);
            pschk.setInt(1, ub.getUserId());
            pschk.setString(2, ub.getPassword());
            ResultSet rschk = pschk.executeQuery();
            while (rschk.next()) {
                count = rschk.getInt(1);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            connchk.close();
        }
        System.out.print(count);
        if (count > 0) {
            
            String sql = "UPDATE apollo.dbo.userBaseInf  SET Password = ?  WHERE userId=? and Password=?";
            Connection conn = DbConn.getCon();
            try {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, ub.getNewpassword());
                ps.setInt(2, ub.getUserId());
                ps.setString(3, ub.getPassword());
                ps.executeUpdate();
                return true;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return false;
            } finally {
                conn.close();
            }
        } else {
            return false;
        }
    }
}
